Round a number

Show Change the number of decimal places displayed, without changing the number

On a worksheet

Select the cells you want to format.
To display more or fewer digits after the decimal point, click Increase Decimal or Decrease Decimal on the Formatting toolbar.

In a built-in number format

On the Format menu, click Cells, and then click the Number tab.
In the Category list, click Currency, Accounting, Percentage, or Scientific.
In the Decimal places box, enter the number of decimal places you want to display.

Show Round a number up

Use the ROUNDUP, EVEN, or ODD functions to do this task.

Worksheet example

The example may be easier to understand if you copy it to a blank worksheet.

Create a blank workbook or worksheet.
Select the example in the Help topic. Do not select the row or column headers.

Selecting an example from Help
Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

1

2
3
4

A
Data
20.3
-5.9
12.5493
Formula	Description (Result)
=ROUNDUP(A2,0)	Rounds 20.3 up to the nearest whole number (21)
=ROUNDUP(A3,0)	Rounds -5.9 up (-6)
=ROUNDUP(A4,2)	Rounds 12.5493 up to the nearest hundredth, two decimal places (12.55)
=EVEN(A2)	Rounds 20.3 up to the nearest even number (22)
=ODD(A2)	Rounds 20.3 up to the nearest odd number (21)

Function details

Show Round a number down

Use the ROUNDDOWN function to do this task.

Worksheet example

The example may be easier to understand if you copy it to a blank worksheet.

Create a blank workbook or worksheet.
Select the example in the Help topic. Do not select the row or column headers.

Selecting an example from Help
Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

1

2
3
4

A
Data
20.3
-5.9
12.5493
Formula	Description (Result)
=ROUNDDOWN(A2,0)	Rounds 20.3 down to the nearest whole number ( 20)
=ROUNDDOWN(A3,0)	Rounds -5.9 down (-5)
=ROUNDDOWN(A4,2)	Rounds the number down to the nearest hundredth, two decimal places ( 12.54)

Functions details

Show Round a number to the nearest number

Use the ROUND function to do this task.

Worksheet example

The example may be easier to understand if you copy it to a blank worksheet.

Create a blank workbook or worksheet.
Select the example in the Help topic. Do not select the row or column headers.

Selecting an example from Help
Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

1

2
3
4

A
Data
20.3
5.9
-5.9
Formula	Description (Result)
=ROUND(A2,0)	Rounds 20.3 down, because the fractional part is less than .5 (20)
=ROUND(A3,0)	Rounds 5.9 up, because the fractional part is greater than .5 (6)
=ROUND(A4,0)	Rounds -5.9 down, because the fractional part is less than -.5 (-6)

Function details

Show Round a number to a near fraction

Use the ROUND function to do this task.

Worksheet example

The example may be easier to understand if you copy it to a blank worksheet.

Create a blank workbook or worksheet.
Select the example in the Help topic. Do not select the row or column headers.

Selecting an example from Help
Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

1

2
3

A
Data
1.25
30.452
Formula	Description (Result)
=ROUND(A2,1)	Rounds the number to the nearest tenth (one decimal place). Because the portion to be rounded is 0.05 or greater, the number is rounded up (result: 1.3)
=ROUND(A3,2)	Rounds the number to the nearest hundredth (two decimal places). Because the portion to be rounded, 0.002, is less than 0.005, the number is rounded down (result: 30.45)

Functions details

Show Round a number to a significant digit above 0

Use the ROUND, ROUNDUP, ROUNDDOWN, LEN, and INT functions to do this task.

Worksheet example

The example may be easier to understand if you copy it to a blank worksheet.

Create a blank workbook or worksheet.
Select the example in the Help topic. Do not select the row or column headers.

Selecting an example from Help
Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

1

2
3

A
Data
5492820
22230
Formula	Description (Result)
=ROUND(A2,3-LEN(INT(A2)))	Rounds the top number to 3 significant digits (5490000)
=ROUNDDOWN(A3,3-LEN(INT(A3)))	Rounds the bottom number down to 3 significant digits (22200)
=ROUNDUP(A2,6-LEN(INT(A2)))	Rounds the top number up to 5 significant digits (5492900)

Function details